﻿using System;
using System.Collections.Generic;
using System.Data;
using SGIPSPManager.Domain;
using Spring.Data.Generic;
using Framework.Core.Domain;
using Spring.Data.Common;
using Spring.Dao;

namespace SGIPSPManager.DAOImpl
{
    public class SPUserMSSQLImpl : AdoDaoSupport, ISPUserDAO
    {
        #region 常量
        private const string SQL_SELECT = "Select * From T_SPUser";
        private const string SQL_SELECT_ONE = "Select * From T_SPUser Where ID = @ID";
        private const string SQL_INSERT = "Insert Into T_SPUser(ID, SPName, SPID, ScrID, NodeID, UserName, Password) Values(@ID, @SPName, @SPID, @ScrID, @NodeID, @UserName, @Password)";
        private const string SQL_DELETE = "Delete From T_SPUser Where 1=1 ";
        private const string SQL_UPDATE = "Update T_SPUser Set SPName=@SPName, SPID=@SPID, ScrID=@ScrID, NodeID=@NodeID, UserName=@UserName, Password=@Password Where 1=1 ";
        #endregion

        #region IDaoBase<SPUser,Guid> Members

        public void Delete(SPUser entity)
        {
            try
            {
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_DELETE + "And ID = @ID", "ID", DbType.Guid, 40, entity.ID);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IList<SPUser> GetAll()
        {
            try
            {
                return AdoTemplate.QueryWithRowMapper<SPUser>(CommandType.Text, SQL_SELECT, new SPUserRowMapper());
            }
            catch (EmptyResultDataAccessException)
            {
                return new List<SPUser>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IList<SPUser> GetByConditions(List<string> conditions, int startIndex, int count, SortInfo sortInfo)
        {
            throw new NotImplementedException();
        }

        public SPUser GetById(Guid entityId, bool shouldLock)
        {
            try
            {
                return AdoTemplate.QueryForObject<SPUser>(CommandType.Text, SQL_SELECT_ONE, new SPUserRowMapper(), "ID", DbType.Guid, 40, entityId);
            }
            catch (EmptyResultDataAccessException)
            {
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int GetCountByConditions(List<string> conditions)
        {
            throw new NotImplementedException();
        }

        public void Save(SPUser entity)
        {
            try
            {
                Guid id = entity.ID == Guid.Empty ? Guid.NewGuid() : entity.ID;
                IDbParameters dbParams = AdoTemplate.CreateDbParameters();
                dbParams.Add("ID", DbType.Guid).Value = id;
                dbParams.Add("SPName", DbType.String).Value = entity.SPName;
                dbParams.Add("SPID", DbType.String).Value = entity.SPID;
                dbParams.Add("ScrID", DbType.String).Value = entity.ScrID;
                dbParams.Add("NodeID", DbType.String).Value = entity.NodeID;
                dbParams.Add("UserName", DbType.String).Value = entity.UserName;
                dbParams.Add("Password", DbType.String).Value = entity.Password;
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_INSERT, dbParams);
                entity.ID = id;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void SaveOrUpdate(SPUser entity)
        {
            if (this.GetById(entity.ID, false) == null)
            {
                this.Save(entity);
            }
            else
            {
                this.Update(entity);
            }
        }

        public void Update(SPUser entity)
        {
            try
            {
                IDbParameters dbParams = AdoTemplate.CreateDbParameters();
                dbParams.Add("ID", DbType.Guid).Value = entity.ID;
                dbParams.Add("SPName", DbType.String).Value = entity.SPName;
                dbParams.Add("SPID", DbType.String).Value = entity.SPID;
                dbParams.Add("ScrID", DbType.String).Value = entity.ScrID;
                dbParams.Add("NodeID", DbType.String).Value = entity.NodeID;
                dbParams.Add("UserName", DbType.String).Value = entity.UserName;
                dbParams.Add("Password", DbType.String).Value = entity.Password;
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_UPDATE + "And ID = @ID", dbParams);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void CreateSPUserMT(SPUser spUser)
        {
            try
            {
                #region 建表命令
                string SQL_Command = string.Format(@"if exists (select 1
                                from  sysobjects
                               where  id = object_id('T_{0}MT')
                                and   type = 'U')
                       drop table T_{0}MT

                    create table T_{0}MT (
                       ID                   varchar(40)          not null,
                       SeqID                bigint               identity,
                       SubmitDate           datetime             null,
                       ScrID             varchar(50)          null,
                       ChargeNumber         varchar(50)          null,
                       UserNumber           varchar(50)          null,
                       SPID               varchar(20)          null,
                       ServiceType          varchar(20)          null,
                       FeeType              int                  null,
                       FeeValue             varchar(20)          null,
                       GivenValue           varchar(20)          null,
                       AgentFlag            int                  null,
                       MorelatetoMTFlag     int                  null,
                       Priority             int                  null,
                       ExpireTime           datetime             null,
                       ScheduleTime         datetime             null,
                       ReportFlag           int                  null,
                       TP_pid               int                  null,
                       TP_udhi              int                  null,
                       MessageCoding        int                  null,
                       MessageType          int                  null,
                       MessageLength        int                  null,
                       MessageContent       varchar(500)         null,
                       Status               int                  null,
                       ReSendTimes          int                  null,
                       constraint PK_T_{0}MT primary key (ID)
                    )

                    declare @CurrentUser sysname
                    select @CurrentUser = user_name()
                    execute sp_addextendedproperty 'MS_Description', 
                       '标识',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'ID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '顺序表示',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'SeqID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '提交时间',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'SubmitDate'

                    execute sp_addextendedproperty 'MS_Description', 
                       'SP的接入号码',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'ScrID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '付费号码，手机号码前加“86”国别标志；当且仅当群发且对用户收费时为空；如果为空，则该条短消息产生的费用由UserNumber代表的用户支付；如果为全零字符串“000000000000000000000”，表示该条短消息产生的费用由SP支付。',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'ChargeNumber'

                    execute sp_addextendedproperty 'MS_Description', 
                       '接收该短消息的手机号，该字段重复UserCount指定的次数，手机号码前加“86”国别标志',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'UserNumber'

                    execute sp_addextendedproperty 'MS_Description', 
                       '企业代码，取值范围0-99999',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'SPID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '业务代码，由SP定义',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'ServiceType'

                    execute sp_addextendedproperty 'MS_Description', 
                       '计费类型',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'FeeType'

                    execute sp_addextendedproperty 'MS_Description', 
                       '取值范围0-99999，该条短消息的收费值，单位为分，由SP定义
                       对于包月制收费的用户，该值为月租费的值
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'FeeValue'

                    execute sp_addextendedproperty 'MS_Description', 
                       '取值范围0-99999，赠送用户的话费，单位为分，由SP定义，特指由SP向用户发送广告时的赠送话费',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'GivenValue'

                    execute sp_addextendedproperty 'MS_Description', 
                       '代收费标志，0：应收；1：实收',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'AgentFlag'

                    execute sp_addextendedproperty 'MS_Description', 
                       '引起MT消息的原因
                       0-MO点播引起的第一条MT消息；
                       1-MO点播引起的非第一条MT消息；
                       2-非MO点播引起的MT消息；
                       3-系统反馈引起的MT消息。
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'MorelatetoMTFlag'

                    execute sp_addextendedproperty 'MS_Description', 
                       '优先级0-9从低到高，默认为0',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'Priority'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息寿命的终止时间，如果为空，表示使用短消息中心的缺省值。时间内容为16个字符，格式为”yymmddhhmmsstnnp” ，其中“tnnp”取固定值“032+”，即默认系统为北京时间',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'ExpireTime'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息定时发送的时间，如果为空，表示立刻发送该短消息。时间内容为16个字符，格式为“yymmddhhmmsstnnp” ，其中“tnnp”取固定值“032+”，即默认系统为北京时间',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'ScheduleTime'

                    execute sp_addextendedproperty 'MS_Description', 
                       '状态报告标记
                       0-该条消息只有最后出错时要返回状态报告
                       1-该条消息无论最后是否成功都要返回状态报告
                       2-该条消息不需要返回状态报告
                       3-该条消息仅携带包月计费信息，不下发给用户，要返回状态报告
                       其它-保留
                       缺省设置为0
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'ReportFlag'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息的编码格式。
                       0：纯ASCII字符串
                       3：写卡操作
                       4：二进制编码
                       8：UCS2编码
                       15: GBK编码
                       其它参见GSM3.38第4节：SMS Data Coding Scheme
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'MessageCoding'

                    execute sp_addextendedproperty 'MS_Description', 
                       '信息类型：
                       0-短消息信息
                       其它：待定
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'MessageType'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息的长度',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'MessageLength'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息的内容',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'MessageContent'

                    execute sp_addextendedproperty 'MS_Description', 
                       '状态
                       0：新信息
                       -1：发送中信息
                       1：成功发送信息
                       2：发送失败信息',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'Status'

                    execute sp_addextendedproperty 'MS_Description', 
                       '重发次数',
                       'user', @CurrentUser, 'table', 'T_{0}MT', 'column', 'ReSendTimes'", spUser.ScrID);
                #endregion
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_Command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void CreateSPUserMTHistory(SPUser spUser)
        {
            try
            {
                #region 建表命令
                string SQL_Command = string.Format(@"if exists (select 1
                                from  sysobjects
                               where  id = object_id('T_{0}MTHistroy')
                                and   type = 'U')
                       drop table T_{0}MTHistroy

                    create table T_{0}MTHistroy (
                       ID                   varchar(40)          not null,
                       SeqID                bigint               identity,
                       SubmitDate           datetime             null,
                       FinishDate           datetime             null,
                       ScrID             varchar(50)          null,
                       ChargeNumber         varchar(50)          null,
                       UserNumber           varchar(50)          null,
                       SPID               varchar(20)          null,
                       ServiceType          varchar(20)          null,
                       FeeType              int                  null,
                       FeeValue             varchar(20)          null,
                       GivenValue           varchar(20)          null,
                       AgentFlag            int                  null,
                       MorelatetoMTFlag     int                  null,
                       Priority             int                  null,
                       ExpireTime           datetime             null,
                       ScheduleTime         datetime             null,
                       ReportFlag           int                  null,
                       TP_pid               int                  null,
                       TP_udhi              int                  null,
                       MessageCoding        int                  null,
                       MessageType          int                  null,
                       MessageLength        int                  null,
                       MessageContent       varchar(500)         null,
                       Status               int                  null,
                       ReSendTimes          int                  null,
                       constraint PK_T_{0}MTHistroy primary key (ID)
                    )

                    declare @CurrentUser sysname
                    select @CurrentUser = user_name()
                    execute sp_addextendedproperty 'MS_Description', 
                       '标识',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'ID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '顺序表示',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'SeqID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '提交时间',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'SubmitDate'

                    execute sp_addextendedproperty 'MS_Description', 
                       '完成时间',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'FinishDate'

                    execute sp_addextendedproperty 'MS_Description', 
                       'SP的接入号码',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'ScrID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '付费号码，手机号码前加“86”国别标志；当且仅当群发且对用户收费时为空；如果为空，则该条短消息产生的费用由UserNumber代表的用户支付；如果为全零字符串“000000000000000000000”，表示该条短消息产生的费用由SP支付。',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'ChargeNumber'

                    execute sp_addextendedproperty 'MS_Description', 
                       '接收该短消息的手机号，该字段重复UserCount指定的次数，手机号码前加“86”国别标志',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'UserNumber'

                    execute sp_addextendedproperty 'MS_Description', 
                       '企业代码，取值范围0-99999',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'SPID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '业务代码，由SP定义',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'ServiceType'

                    execute sp_addextendedproperty 'MS_Description', 
                       '计费类型',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'FeeType'

                    execute sp_addextendedproperty 'MS_Description', 
                       '取值范围0-99999，该条短消息的收费值，单位为分，由SP定义
                       对于包月制收费的用户，该值为月租费的值
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'FeeValue'

                    execute sp_addextendedproperty 'MS_Description', 
                       '取值范围0-99999，赠送用户的话费，单位为分，由SP定义，特指由SP向用户发送广告时的赠送话费',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'GivenValue'

                    execute sp_addextendedproperty 'MS_Description', 
                       '代收费标志，0：应收；1：实收',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'AgentFlag'

                    execute sp_addextendedproperty 'MS_Description', 
                       '引起MT消息的原因
                       0-MO点播引起的第一条MT消息；
                       1-MO点播引起的非第一条MT消息；
                       2-非MO点播引起的MT消息；
                       3-系统反馈引起的MT消息。
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'MorelatetoMTFlag'

                    execute sp_addextendedproperty 'MS_Description', 
                       '优先级0-9从低到高，默认为0',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'Priority'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息寿命的终止时间，如果为空，表示使用短消息中心的缺省值。时间内容为16个字符，格式为”yymmddhhmmsstnnp” ，其中“tnnp”取固定值“032+”，即默认系统为北京时间',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'ExpireTime'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息定时发送的时间，如果为空，表示立刻发送该短消息。时间内容为16个字符，格式为“yymmddhhmmsstnnp” ，其中“tnnp”取固定值“032+”，即默认系统为北京时间',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'ScheduleTime'

                    execute sp_addextendedproperty 'MS_Description', 
                       '状态报告标记
                       0-该条消息只有最后出错时要返回状态报告
                       1-该条消息无论最后是否成功都要返回状态报告
                       2-该条消息不需要返回状态报告
                       3-该条消息仅携带包月计费信息，不下发给用户，要返回状态报告
                       其它-保留
                       缺省设置为0
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'ReportFlag'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息的编码格式。
                       0：纯ASCII字符串
                       3：写卡操作
                       4：二进制编码
                       8：UCS2编码
                       15: GBK编码
                       其它参见GSM3.38第4节：SMS Data Coding Scheme
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'MessageCoding'

                    execute sp_addextendedproperty 'MS_Description', 
                       '信息类型：
                       0-短消息信息
                       其它：待定
                       ',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'MessageType'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息的长度',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'MessageLength'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息的内容',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'MessageContent'

                    execute sp_addextendedproperty 'MS_Description', 
                       '状态
                       0：新信息
                       -1：发送中信息
                       1：成功发送信息
                       2：发送失败信息',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'Status'

                    execute sp_addextendedproperty 'MS_Description', 
                       '重发次数',
                       'user', @CurrentUser, 'table', 'T_{0}MTHistroy', 'column', 'ReSendTimes'", spUser.ScrID);
                #endregion
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_Command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void CreateSPUserMO(SPUser spUser)
        {
            try
            {
                #region 建表命令
                string SQL_Command = string.Format(@"if exists (select 1 from  sysobjects where  id = object_id('T_{0}MO') and  type = 'U')
                       drop table T_{0}MO

                    create table T_{0}MO (
                       ID                   bigint               identity,
                       DeliverDate          datetime             null,
                       ScrID             varchar(50)          null,
                       TP_pid               int                  null,
                       TP_udhi              int                  null,
                       MessageCoding        int                  null,
                       MessageLength        int                  null,
                       MessageContent       varchar(500)         null,
                       constraint PK_T_{0}MO primary key (ID)
                    )

                    declare @CurrentUser sysname
                    select @CurrentUser = user_name()
                    execute sp_addextendedproperty 'MS_Description', 
                       '标识',
                       'user', @CurrentUser, 'table', 'T_{0}MO', 'column', 'ID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '到达时间',
                       'user', @CurrentUser, 'table', 'T_{0}MO', 'column', 'DeliverDate'

                    execute sp_addextendedproperty 'MS_Description', 
                       'SP接入号',
                       'user', @CurrentUser, 'table', 'T_{0}MO', 'column', 'ScrID'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息编码',
                       'user', @CurrentUser, 'table', 'T_{0}MO', 'column', 'MessageCoding'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息的长度',
                       'user', @CurrentUser, 'table', 'T_{0}MO', 'column', 'MessageLength'

                    execute sp_addextendedproperty 'MS_Description', 
                       '短消息的内容',
                       'user', @CurrentUser, 'table', 'T_{0}MO', 'column', 'MessageContent'", spUser.ScrID);
                #endregion
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_Command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DropSPUserMT(SPUser spUser)
        {
            try
            {
                #region 建表命令
                string SQL_Command = string.Format(@"if exists (select 1
                            from  sysobjects
                           where  id = object_id('T_{0}MT')
                            and   type = 'U')
                   drop table T_{0}MT", spUser.ScrID);
                #endregion
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_Command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DropSPUserMTHistory(SPUser spUser)
        {
            try
            {
                #region 建表命令
                string SQL_Command = string.Format(@"if exists (select 1
                            from  sysobjects
                           where  id = object_id('T_{0}MTHistory')
                            and   type = 'U')
                   drop table T_{0}MTHistory", spUser.ScrID);
                #endregion
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_Command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DropSPUserMO(SPUser spUser)
        {
            try
            {
                #region 建表命令
                string SQL_Command = string.Format(@"if exists (select 1
                            from  sysobjects
                           where  id = object_id('T_{0}MO')
                            and   type = 'U')
                   drop table T_{0}MO", spUser.ScrID);
                #endregion
                AdoTemplate.ExecuteNonQuery(CommandType.Text, SQL_Command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
